package com.cindata

import com.cindata.dictionary.*
import grails.gorm.transactions.Transactional
import net.sf.json.JSON
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.formula.functions.T
import org.grails.plugins.excelimport.AbstractExcelImporter
import org.springframework.security.access.annotation.Secured

import java.text.SimpleDateFormat
import static org.springframework.http.HttpStatus.*

@Secured(['isAuthenticated()'])
@Transactional
class EstateImportBatchController
{

    static allowedMethods = [save: "POST", update: "PUT", delete: "DELETE"]
    def evaluateService
    def excelExportService
    def sessionFactory
    def springSecurityService
    def componentService
    def dataSource
    def estateBatchEvaluateService
    def jdbcService


    def index(Integer max)
    {
        params.max = Math.min(max ?: 10, 100)
        def order = params["order"]
        def sort = params["sort"]
        String sql = " from EstateImportBatch e where 1=1"
        def user = User.findById(springSecurityService.principal.id)
        def accountSql = Account.FilterAccountSql(user)
        if (accountSql)
        {
            sql += " and e.createBy.account.id in ${accountSql}"
        }

        String sql1 = "select count(e.id) " + sql
        if (order && sort)
        {
            sql += " order by e.${sort} ${order}"
        }
        else
        {
            sql += " order by e.modifiedDate desc "
        }
        def estateImportBatchList = EstateImportBatch.executeQuery(sql, [max: params.max ?: 10, offset: params.offset ?: 0])
        def estateImportBatchCount = EstateImportBatch.executeQuery(sql1)

        respond EstateImportBatch, model: [estateImportBatchList: estateImportBatchList, estateImportBatchCount: estateImportBatchCount[0]]
    }

    def show(EstateImportBatch estateImportBatch)
    {
        respond estateImportBatch
    }

    @Transactional
    def ajaxSearch()
    {
        def batchs = params["batchs"]
        def result = false
        def sql = "from EstateImportBatch e where 1=1 "
        def user = User.findById(springSecurityService.principal.id)
        def accountSql = Account.FilterAccountSql(user)
        if (accountSql)
        {
            sql += " and e.createBy.account.id in ${accountSql}"
        }
        if (batchs)
        {
            sql += " and e.batchNumber = '${batchs}' "
        }
        def batchList = EstateImportBatch.executeQuery(sql).asList()
        if (batchList.size() != 0)
        {
            result = true
        }
        else
        {
            result = false
        }
        render([result: result] as JSON)
    }

    @Transactional
    def searchEstate()
    {
        def order = params["order"]
        def sort = params["sort"]
        def batchs = params["batchs"]
        //批次号
        def createBy = params["createBy"]
        //导入经手人
        def status = params['status']
        //导入状态
        def evaluateType = params['evaluateType']
        //处理方式
        def startDate = params["startDate"]
        //开始时间
        def endDate = params["endDate"]
        //结束时间
        def importFileType = params["importFileType"]
        def sqlParam = [:]
        //导入文件类型
        String sql = "from EstateImportBatch e where 1=1"
        def user = User.findById(springSecurityService.principal.id)
        def accountSql = Account.FilterAccountSql(user)
        if (accountSql)
        {
            sql += " and e.createBy.account.id in ${accountSql}"
        }
        if (batchs)
        {
            sql += " and e.batchNumber like '%${batchs}%'"
        }
        if (status)
        {
            sql += " and e.status.name = '${status}'"
        }
        if (evaluateType)
        {
            sql += " and e.evaluateType.id= '${evaluateType}'"
        }
        if (startDate && endDate)
        {
            //中文日期转换
            sqlParam.startDate = DateUtil.GetDateBeginByDateStr(startDate.toString())
            sqlParam.endDate = DateUtil.GetNextDateBeginByDateStr(endDate.toString())
            sql += "and e.createdDate >= :startDate and e.createdDate  < :endDate "
        }
        if (createBy)
        {
            sql += "and e.createBy.id = '${createBy}'"
        }
        if (importFileType)
        {
            sql += "and e.importFileType.id = '${importFileType}'"
        }

        //查询总数
        String countSql = "select count(e.id) " + sql

        if (order && sort)
        {
            sql += " order by e."+sort+" "+order
        }
        else
        {
            sql += "order by e.modifiedDate desc "
        }
        //执行查询方法
        def estateImportBatchList = EstateImportBatch.executeQuery(sql, sqlParam, [max: params.max ?: 10, offset: params.offset ?: 0])
        def count = EstateImportBatch.executeQuery(countSql, sqlParam)
        if (estateImportBatchList != null)
        {

            respond EstateImportBatch, model: [estateImportBatchList: estateImportBatchList, estateImportBatchCount: count[0], params: params], view: 'index'
        }
        else
        {
            respond EstateImportBatch, view: 'index'
        }
    }

    def create()
    {
        respond new EstateImportBatch(params)
    }

    @Transactional
    def excelImport()
    {
        /**
         * 读取文件*/
        def file = request.getFile('myFile')
        def iftCode = params["importFileType"]
        //将文件传给对应类,读取文件
        AbstractExcelImporter importer
        if (iftCode == "Estate")
        {
            importer = new EstateExcelImporter()
        }
        else if (iftCode == "Repayment")
        {
            importer = new RepaymentExcelImporter()
        }
        importer.read(file.getInputStream())
        def estateMapList = importer.getEstates()
        importer.ge

        //获取集合
        def estateNum = estateMapList.size()

        def batch = params["batch"]

        def createBy = User.findByUsername(sec.loggedInUserInfo(field: "username"))
        def currentDate = new Date()
        def importFileType = ImportFileTypeDict.findByCode(iftCode)
        /**
         * 加入批次信息*/
        Map param = [batchNumber: batch,
            importFileType: importFileType,
            estateNum: estateNum,
            matchNum: 0,
            status: ExecStatusDict.EXECUTING(),
            createBy: createBy,
            isDeleted: 0]

        def newImportBatch = new EstateImportBatch(param)

        //将批次信息加入批次表中
        if (!newImportBatch.save())
        {
            println "EstateImportBatch not saved, errors = ${newImportBatch.errors}"
            respond EstateImportBatch, model: ["message": "导入失败", "success": false], view: "message"
        }

        println '批次ID: [${newEstateImportBatch.id}]'


        /**
         * 遍历存入excel里的数据*/
        Thread.start {
            evaluateService.batchInserts(estateMapList, newImportBatch, currentDate)
            if (iftCode == 'estate')
            {
                // 如果是押品导入才开始评估
                evaluateService.evaluateBatch(newImportBatch.getId())
            }
        }
        respond EstateImportBatch, model: ["success": true, "key": newImportBatch.id], view: "message"
        return
    }

    @Transactional
    def csvImport()
    {
        /**
         * 读取文件*/
        def file = request.getFile('myFile')
        def fileCharset = FileUtil.GetFileCharset(file.getInputStream())
        def iftCode = params["importFileType"]
        def evaluateType = params["evaluateType"]

        //将文件传给对应类,读取文件
        def importer
        if (iftCode == "Estate")
        {
            importer = new EstateCSVImporter()
        }
        else if (iftCode == "Repayment")
        {
            importer = new RepaymentCSVImporter()
        }
        def estateMapList = importer.read(file.getInputStream(), fileCharset)

        //获取集合
        def estateNum = estateMapList?.size()
        if (estateNum == 0) 
        {
            respond EstateImportBatch, model: ["error": "模板数据为空！", "success": false], view: "message"
            return
        }

        def sdf = new SimpleDateFormat("yyyyMMddHHmmss")
        def batch = sdf.format(new Date())

        def createBy = User.findByUsername(sec.loggedInUserInfo(field: "username"))
        def currentDate = new Date()
        def importFileType = ImportFileTypeDict.findByCode(iftCode)

        def evaluateTypeTemp
        if (evaluateType == "1")
        {
            evaluateTypeTemp = EvaluateDict.AUTOMATIC()
        } 
        else if (evaluateType == "7")
        {
            evaluateTypeTemp = EvaluateDict.ARTIFICIAL()
        } 
        else if (evaluateType == "8")
        {
            evaluateTypeTemp = EvaluateDict.AUTOANDARTIFICIAL()
        }
        if (iftCode == "Repayment")
        {
            evaluateTypeTemp = null
        }
        /**
         * 加入批次信息*/
        Map param = [batchNumber: batch,
            importFileType: importFileType,
            estateNum: estateNum,
            matchNum: 0,
            status: ExecStatusDict.EXECUTING(),
            createBy: createBy,
            account: createBy?.account,
            isDeleted: 0,
            evaluateType: evaluateTypeTemp,
            startTime: new Date(),
            type: '导入'
            ]

        if (iftCode == "Repayment")
        {
            param.status = ExecStatusDict.SUCCESS()
        }
        def newImportBatch = new EstateImportBatch(param)

        //将批次信息加入批次表中
        if (!newImportBatch.save(flush:true))
        {
            println "EstateImportBatch not saved, errors = ${newImportBatch.errors}"
            respond EstateImportBatch, model: ["error": "导入失败", "success": false], view: "message"
        }

        println '批次ID: [' + newImportBatch?.id + "]"

        /**
         * 遍历存入excel里的数据*/
        Thread.start 
        {
            Thread.sleep(2000)
            def sdfd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")

            println sdfd.format(new Date())
            println "1.批次押品临时表"
            evaluateService.batchInserts(estateMapList, newImportBatch, currentDate)
            if (iftCode == 'Estate')
            {
                println sdfd.format(new Date())
                println "2.批次押品评估"
                evaluateService.evaluateBatch(newImportBatch.getId(), evaluateType?.toInteger())

                println sdfd.format(new Date())
                println "3.修改押品批次评估状态"
                println newImportBatch?.id
                evaluateService.updateBatchCount(newImportBatch)

                println sdfd.format(new Date())
                println "4.押品记录estate"
                evaluateService.estateInsert(newImportBatch)

                println sdfd.format(new Date())
                println "5.押品记录collateral"
                evaluateService.collateralInserts(newImportBatch)

                println sdfd.format(new Date())
                println "6.贷款、借款人记录"
                evaluateService.mortgageInserts(estateMapList, newImportBatch)

                println sdfd.format(new Date())
                println "7.生成评估记录"
                evaluateService.valuationHistorySuccess(newImportBatch)
                println sdfd.format(new Date())
            }
            if (iftCode == 'Repayment') 
            {
                evaluateService.updateBatchRepaymentCount(newImportBatch)
            }

            evaluateService.updateBatchTime(newImportBatch)
        }

        respond EstateImportBatch, model: ["success": true, "key": newImportBatch.id], view: "message"
        return
    }

    def getProgress(Long progressKey)
    {
        def progress = evaluateService.getProgress(progressKey)
        render([progress: progress] as grails.converters.JSON)
    }

    @Transactional
    def save(EstateImportBatch estateImportBatch)
    {
        if (estateImportBatch == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        if (estateImportBatch.hasErrors())
        {
            transactionStatus.setRollbackOnly()
            respond estateImportBatch.errors, view: 'create'
            return
        }

        estateImportBatch.save flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.created.message', args: [message(code: 'estateImportBatch.label', default: 'EstateImportBatch'), estateImportBatch.id])
                redirect estateImportBatch
            }
            '*' { respond estateImportBatch, [status: CREATED] }
        }
    }

    def exportFailureEstate(Long id)
    {
        //根据批次ID导出失败数据
        def batch = EstateImportBatch.findById(id)
        String sql = "from EstateImport where batch.id = " + batch.id + " and evaluateStatue.name = '失败'"
        def result = EstateImport.executeQuery(sql)
        //导出数据
        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("处理失败押品", "UTF-8") + ".xls")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()
        //提前还款情况报表
        String[] headers = ["押品编号", "押品状态", "城市名称", "行政区名称", "地址", "面积（平方米）", "总楼层", "当前层", "贷款合同号", "处理状态", "失败原因"]
        String[] params = ["externalId", "estateStatus", "cityName", "districtName", "address", "buildArea", "totalFloor", "currentFloor", "serialNumber", "status", "failReason"]
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        result.each {
            print(it.evaluateStatue.name)
            Map<String, T> m = new HashMap<String, T>();
            m.put("externalId", it?.externalId)
            m.put("estateStatus", it?.estateStatus?.name)
            m.put("cityName", it?.cityName)
            m.put("districtName", it?.districtName)
            m.put("address", it?.address)
            m.put("buildArea", it?.buildArea)
            m.put("totalFloor", it?.totalFloor)
            m.put("currentFloor", it?.currentFloor)
            m.put("serialNumber", it?.serialNumber)
            m.put("status", "失败")
            m.put("failReason", it?.failReason)

            listn.add(m)
        }
        HSSFWorkbook workbook = excelExportService.exportExcel("处理失败押品", headers, listn, params);
        workbook.write(response.getOutputStream());
        response.flushBuffer() //立即清空输出
        response.getOutputStream().close();
        println("下载完成")
    }

    def edit(EstateImportBatch estateImportBatch)
    {
        respond estateImportBatch
    }

    @Transactional
    def update(EstateImportBatch estateImportBatch)
    {
        if (estateImportBatch == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        if (estateImportBatch.hasErrors())
        {
            transactionStatus.setRollbackOnly()
            respond estateImportBatch.errors, view: 'edit'
            return
        }

        estateImportBatch.save flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.updated.message', args: [message(code: 'estateImportBatch.label', default: 'EstateImportBatch'), estateImportBatch.id])
                redirect estateImportBatch
            }
            '*' { respond estateImportBatch, [status: OK] }
        }
    }

    @Transactional
    def delete(EstateImportBatch estateImportBatch)
    {

        if (estateImportBatch == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        estateImportBatch.delete flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.deleted.message', args: [message(code: 'estateImportBatch.label', default: 'EstateImportBatch'), estateImportBatch.id])
                redirect action: "index", method: "GET"
            }
            '*' { render status: NO_CONTENT }
        }
    }

    protected void notFound()
    {
        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.not.found.message', args: [message(code: 'estateImportBatch.label', default: 'EstateImportBatch'), params.id])
                redirect action: "index", method: "GET"
            }
            '*' { render status: NOT_FOUND }
        }
    }

    // 失败押品转人工
    @Transactional
    def evaluateFailureEstate(Long id)
    {
        def estateImportBatch = EstateImportBatch.findById(id)
        estateImportBatch.evaluateType = EvaluateDict.AUTOANDARTIFICIAL()
        estateImportBatch.status = ExecStatusDict.EXECUTING()
        estateImportBatch.matchNum = estateImportBatch.matchNum - EstateImport.countByBatchAndEvaluateStatue(estateImportBatch, ExecStatusDict.FAILURE())
        estateImportBatch.modifiedDate = new Date()
        if (estateImportBatch.validate()) 
        {
            estateImportBatch.save flush: true
        }
        else
        {
            println estateImportBatch.errors
        }

        Thread.start
        {
            this.sleep(20000)
            evaluateService.evaluateFailureEstate(id)
            evaluateService.updateBatchCount(estateImportBatch)
        }

        flash.message = "失败押品已转人工评估处理！"
        redirect action: 'index'
    }

    // 失败押品转人工
    @Transactional
    def reEvaluateFailureEstate(Long id)
    {
        def estateImportBatch = EstateImportBatch.findById(id)
        estateImportBatch.evaluateType = EvaluateDict.AUTOANDARTIFICIAL()
        estateImportBatch.status = ExecStatusDict.EXECUTING()
        estateImportBatch.matchNum = estateImportBatch.matchNum - BatchEstate.countByBatchAndEvaluateStatue(estateImportBatch, ExecStatusDict.FAILURE())
        estateImportBatch.modifiedDate = new Date()
        if (estateImportBatch.validate()) 
        {
            estateImportBatch.save flush: true
        }
        else
        {
            println estateImportBatch.errors
        }

        Thread.start
        {
            this.sleep(20000)
            estateBatchEvaluateService.reEvaluateFailureEstate(id)
            estateBatchEvaluateService.updateBatchEstateCount(estateImportBatch)
        }

        flash.message = "失败押品已转人工评估处理！"
        redirect action: 'index'
    }

    def evaluateAccountBatchIndex()
    {
        def user = User.findById(springSecurityService.principal.id)
        def sql = "from EstateImportBatch e where e.latestEvaluateAccount.id = ${user.account?.id}"
        def batch = params['batchName']
        if (batch) 
        {
            sql += " and e.batchNumber like '%${batch}%'"
        }
        def status = params['status']
        if (status)
        {
            sql += " and e.status.name = '${status}'"
        }
        def countSql = "select count(e.id) " + sql
        sql += " order by e.modifiedDate desc"
        
        def estateImportBatchList = EstateImportBatch.executeQuery(sql, [max: params.max ?: 10, offset: params.offset ?: 0])
        def estateImportBatchCount = EstateImportBatch.executeQuery(countSql)
        respond EstateImportBatch, model: [estateImportBatchList: estateImportBatchList, estateImportBatchCount: estateImportBatchCount[0], params: params]
    }

    // 评估机构下载文件
    def evaluateAccountBatchExport(Long id)
    {
        def batch = EstateImportBatch.findById(id)
        String sql = "select estate.externalId, estate.buildingType.name, estate.estateStatus.name, estate.city.name, estate.district.name, estate.sourceAddress, estate.sourceBuildArea, estate.sourceTotalFloor, estate.sourceCurrentFloor from BatchEstate where batch.id = " + batch.id
        def list = EstateImport.executeQuery(sql)
        
        def listTitle = new ArrayList<Map>(Arrays.asList("押品编号", "房屋用途", "押品状态", "城市名称", "行政区名称", "地址", "面积（平方米）", "总楼层", "当前层", "最新评估价值", "最新评估日期"))
        response.setContentType("text/csv")
        response.setContentType("application/csv;charset=UTF-8")
        response.setHeader("Content-Disposition", "attachment;FileName=${batch?.batchNumber}.csv")
        OutputStream out = null
        try
        {
            out = response.getOutputStream()
        }
        catch (Exception e)
        {
            e.printStackTrace()
        }
        excelExportService.exportExcelData(out, listTitle, list)
        return null
    }

    // 评估机构上传文件
    def evaluateAccountBatchImport()
    {
        def batchNumber = params['batchNumberValue']
        def file = request.getFile('myFile')
        def fileOrgName = file?.getOriginalFilename()
        def fileType = fileOrgName?.split('\\.')[-1]
        fileType = fileType?.toLowerCase()

        File fileImage = new File(servletContext.getRealPath("/"), "images/${batchNumber}.${fileType}")
        file.transferTo(fileImage)

        respond EstateImportBatch, model: ["success": true, "key": "evaluateAccountBatchImport"], view: "message"
    }

    // 银行机构导入
    def bankAccountBatchImport(EstateImportBatch estateImportBatch)
    {
        def batchNumber = estateImportBatch?.batchNumber
        def webRootDir = servletContext.getRealPath("/")
        File file = new File(webRootDir, "images/${batchNumber}.csv")
        if (file.exists() && file.isFile())
        {
            Thread.start
            {
                evaluateService.bankAccountBatchImport(webRootDir, file, estateImportBatch.getId())
            }
        }
        else
        {
            flash.message = "对不起，文件不存在！"
            redirect action: "index"
            return
        }

        flash.message = "正在进行评估！"
        redirect action: "index"
    }

    def estateImportBatchShow(EstateImportBatch estateImportBatch)
    {
        def max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def offset = params.offset ? params.offset.toInteger() : 0

        def batchEstateList = BatchEstate.findAllByBatch(estateImportBatch, [max: max, offset: offset])
        respond estateImportBatch, model: [batchEstateList: batchEstateList, batchEstateCount: BatchEstate.countByBatch(estateImportBatch)]
    }

    def estateImportBatchEdit(EstateImportBatch estateImportBatch)
    {
        params['targetUri'] = request.getHeader("referer")
        respond estateImportBatch
    }

    @Transactional
    def estateImportBatchUpdate(EstateImportBatch estateImportBatch)
    {
        if (estateImportBatch == null)
        {
            transactionStatus.setRollbackOnly()
            notFound()
            return
        }

        if (estateImportBatch.hasErrors())
        {
            transactionStatus.setRollbackOnly()
            respond estateImportBatch.errors, view: 'estateImportBatchEdit'
            return
        }

        estateImportBatch.save flush: true

        request.withFormat {
            form multipartForm {
                flash.message = message(code: 'default.created.message', args: [message(code: 'estateImportBatch.label', default: 'EstateImportBatch'), estateImportBatch.id])
                redirect url: params['targetUri']
            }
            '*' { respond estateImportBatch, [status: CREATED] }
        }
    }
    
    def estateBatchReValuation(EstateImportBatch estateImportBatch)
    {
        def evaluateType = estateImportBatch?.evaluateType
        if (!evaluateType)
        {
            flash.message = "请选择评估方式"
            redirect action: 'estateImportBatchShow', id: estateImportBatch?.id
            return
        }

        if (evaluateType == EvaluateDict.AUTOMATIC())
        {
            evaluateType = 1
        } 
        if (evaluateType == EvaluateDict.ARTIFICIAL())
        {
            evaluateType = 7
        } 
        if (evaluateType == EvaluateDict.AUTOANDARTIFICIAL())
        {
            evaluateType = 8
        }
        if (evaluateType == EvaluateDict.EVALUATEACCOUNT())
        {
            def batchAccountCount = BatchAccount.countByBatch(estateImportBatch)
            if (batchAccountCount == 0)
            {
                flash.message = "请选择评估机构"
                redirect action: 'estateImportBatchShow', id: estateImportBatch?.id
                return
            }
            if (batchAccountCount > 1 && !estateImportBatch?.distributeRule)
            {
                flash.message = "请选择评估分发策略"
                redirect action: 'estateImportBatchShow', id: estateImportBatch?.id
                return
            }
            evaluateType = 10
        }

        // 更显批次状态为执行中
        def evaluateStatue = ExecStatusDict.EXECUTING()
        estateImportBatch.status = evaluateStatue
        estateImportBatch.matchNum = 0
        estateImportBatch.startTime = new Date()
        estateImportBatch.save flush: true

        def sql = new groovy.sql.Sql(dataSource)
        sql.execute("update batch_estate set evaluate_statue_id = ? where batch_id = ?",[evaluateStatue?.id, estateImportBatch?.id])
        sql.close()

        // 重估
        if (evaluateType != 10)
        {
            Thread.start
            {
                this.sleep(2000)

                estateBatchEvaluateService.evaluateBatch(estateImportBatch.getId(), evaluateType)
                estateBatchEvaluateService.valuationHistorySuccess(estateImportBatch)
                estateBatchEvaluateService.updateBatchEstateCount(estateImportBatch)
            }
        }
        else
        {
            // 评估机构重估
            // 评估策略
            def latestEvaluateAccount = null
            def accounts = BatchAccount.findAllByBatch(estateImportBatch)*.account
            if (accounts?.size() == 1) 
            {
                latestEvaluateAccount = accounts[0]
            }
            else
            {
                def serviceParams = [estateImportBatch: estateImportBatch]
                def distributeRule = estateImportBatch.distributeRule

                latestEvaluateAccount = componentService.evaluate(distributeRule, serviceParams)
            }

            estateImportBatch.latestEvaluateAccount = latestEvaluateAccount
            estateImportBatch.evaluateType = EvaluateDict.EVALUATEACCOUNT()
            estateImportBatch.status = ExecStatusDict.EXECUTING()
            estateImportBatch.matchNum = 0
            estateImportBatch.modifiedDate = new Date()
            estateImportBatch.startTime = new Date()
            if (estateImportBatch.validate()) 
            {
                estateImportBatch.save flush: true
            }
            else
            {
                println estateImportBatch.errors
            }
            batchEstateList?.each
            {
                it?.latestEvaluateAccount = latestEvaluateAccount
                it?.evaluateStatue = evaluateStatue
                it?.modifiedDate = new Date()
                it?.save flush: true
            }

            flash.message = "已上传到评估机构！"
            redirect controller: 'estateImportBatch', action: 'index'
            return

        }

        flash.message = "正在进行评估！"
        redirect controller: 'estateImportBatch', action: 'index'
    }

    def exportFailureBatchEstate(Long id)
    {
        println new Date()
        def batch = EstateImportBatch.findById(id)
        String sql = "from BatchEstate where batch.id = " + batch.id + " and evaluateStatue.name = '失败'"
        def result = BatchEstate.executeQuery(sql)
        //导出数据
        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("处理失败押品", "UTF-8") + ".xls")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()

        String[] headers = ["批次号", "押品编号", "押品状态", "城市名称", "行政区名称", "地址", "面积（平方米）", "总楼层", "当前层", "处理状态", "失败原因"]
        String[] params = ["batchNumber", "externalId", "estateStatus", "cityName", "districtName", "address", "buildArea", "totalFloor", "currentFloor", "status", "failReason"]
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        result.each {
            Map<String, T> m = new HashMap<String, T>();
            m.put("batchNumber", it?.batch?.batchNumber)
            m.put("externalId", it?.estate?.externalId)
            m.put("estateStatus", it?.estate?.estateStatus?.name)
            m.put("cityName", it?.estate?.city?.name)
            m.put("districtName", it?.estate?.district?.name)
            m.put("address", it?.estate?.sourceAddress)
            m.put("buildArea", it?.estate?.sourceBuildArea)
            m.put("totalFloor", it?.estate?.sourceTotalFloor)
            m.put("currentFloor", it?.estate?.sourceCurrentFloor)
            m.put("status", "失败")
            m.put("failReason", it?.failReason)

            listn.add(m)
        }
        HSSFWorkbook workbook = excelExportService.exportExcel("处理失败押品", headers, listn, params);
        workbook.write(response.getOutputStream());
        response.flushBuffer() //立即清空输出
        response.getOutputStream().close();
        println("下载完成")
        println new Date()
    }

    def exportFailureBatchEstate1(Long id)
    {
        println new Date()
        def execStatusId = ExecStatusDict.FAILURE()?.id
        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("处理失败押品", "UTF-8") + ".csv")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()

        String[] headers = ["批次号", "押品编号", "押品状态", "城市名称", "行政区名称", "地址", "面积（平方米）", "总楼层", "当前层", "处理状态", "失败原因"]
        String[] params = ["a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k"]
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        
       // def sql = new groovy.sql.Sql(dataSource)
        String sql ="SELECT " +
                "eib.batch_number AS A, " +
                "e.external_id AS b, " +
                "dd. NAME AS C, " +
                "a1. NAME AS d, " +
                "a2. NAME AS e, " +
                "e.source_address AS f, " +
                "e.source_build_area AS G, " +
                "e.source_total_floor AS h, " +
                "e.source_current_floor AS i, " +
                "'失败' AS j, " +
                "b.fail_reason AS K " +
                "FROM " +
                "batch_estate AS b " +
                "LEFT JOIN estate AS e ON b.estate_id = e. ID " +
                "LEFT JOIN estate_import_batch eib ON b.batch_id = eib. ID " +
                "LEFT JOIN data_dict dd ON dd. ID = e.estate_status_id " +
                "LEFT JOIN area a1 ON a1.area_id = e.city_id " +
                "LEFT JOIN area a2 ON a2.area_id = e.district_id " +
                " where eib.id = ${id}" +
                " and b.evaluate_statue_id = ${execStatusId}";
                //"LIMIT 100000;";
       // sql.close()
        println ("查询开始"+new Date())
        def list = jdbcService.findBySql(sql)
        println ("查询结束"+new Date())
        println list.size()+"----"
        //HSSFWorkbook workbook = excelExportService.exportExcel("处理失败押品", headers, list, params);
        println ("导出开始"+new Date())
        excelExportService.exportExcel1(response.getOutputStream(),"处理失败押品", headers, list, params);
        println ("导出结束"+new Date())
       // response.getOutputStream().write(sbf.getBytes());
       // excelExportService.exportExcelData( response.getOutputStream(),headers,list)
        response.flushBuffer()
        response.getOutputStream().close();
        println("下载完成")
        println new Date()
    }

    def jvm(){
        println "总分配内存"+Runtime.getRuntime().totalMemory()/1024/1024+"MB";
        println "最大内存"+Runtime.getRuntime().maxMemory()/1024/1024+"MB";
        println "空闲内存"+Runtime.getRuntime().freeMemory()/1024/1024+"MB";
        render "success";
    }
}